<%@page session="false"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.net.URLEncoder"%>
<%@page import="java.sql.Connection"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.Timestamp"%>
<%@page import="java.text.ParseException"%>
<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.util.Calendar"%>
<%@page import="java.util.Date"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.GregorianCalendar"%>
<%@page import="java.util.TimeZone"%>
<%@page import="java.util.concurrent.TimeUnit"%>
<%@page import="java.io.PrintWriter"%>
<%@page import="wt.method.MethodContext"%>
<%@page import="wt.pom.WTConnection"%>
<%@page import="wt.util.WTContext"%>
<%@page import="wt.util.jmx.AccessUtil"%>

<%
 // only allow system administrators to access this page
 if ( !AccessUtil.isSiteAdministrator() )
 {
   response.sendError( HttpServletResponse.SC_FORBIDDEN );
   return;
 }

 SimpleDateFormat  dateFormat = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss.SSS Z" );
 dateFormat.setTimeZone( WTContext.getContext().getTimeZone() );
 Date curDate = new Date();
 String sqlQuery = "select TopSQLStats.JVM_Id, TopSQLStats.JVM_StartTime, StartTime, LE_Timestamp as EndTime, max( ElapsedSeconds ) as MaxSeconds, "
    +"ServiceName from TopSQLStats left outer join ( select distinct JVM_Id, JVM_StartTime, ServiceName from MethodServerInfo ) MethodServerInfo on "
    +"TopSQLStats.JVM_Id = MethodServerInfo.JVM_Id and TopSQLStats.JVM_StartTime = MethodServerInfo.JVM_StartTime where LE_Timestamp >= ? and StartTime"
    +" <= ? group by TopSQLStats.JVM_Id, TopSQLStats.JVM_StartTime, StartTime, LE_Timestamp, ServiceName order by StartTime, LE_Timestamp, JVM_Id, JVM_StartTime";

 final Connection  connection = ((WTConnection)MethodContext.getContext().getConnection()).getConnection();  // use normal pooled Windchill connection
 final PreparedStatement  statement = connection.prepareStatement( sqlQuery );
 statement.setTimestamp( 1, new Timestamp( curDate.getTime()-1000*60*15 ), dbStorageCalendar );
 statement.setTimestamp( 2, new Timestamp( curDate.getTime() ), dbStorageCalendar );
 
 ArrayList<ArrayList> resultList=new ArrayList<ArrayList>();
 int resultSize=0;
 try{
     final ResultSet  results = statement.executeQuery();
     //print start
     /*
     out.println("<table border=1>");
     out.println("<tr><td>JVM_Id</td><td>JVM_StartTime</td><td>StartTime</td><td>Endtime</td><td>MaxSeconds</td><td>ServiceName</td></tr>");
     for ( int row = 1; results.next(); ++row )
     {
         out.println("<tr>");
         out.println("<td>"+results.getString(1)+"</td>");
         out.println("<td>"+dateFormat.format(results.getTimestamp(2,dbStorageCalendar))+"</td>");
         out.println("<td>"+dateFormat.format(results.getTimestamp(3,dbStorageCalendar))+"</td>");
         out.println("<td>"+dateFormat.format(results.getTimestamp(4,dbStorageCalendar))+"</td>");
         out.println("<td>"+results.getDouble(5)+"</td>");
         out.println("<td>"+results.getString(6)+"</td>");
         out.println("</tr>");
     }*/
     //print  end
     
     for ( int row = 1; results.next(); ++row )
     {
         ArrayList<Object> rowList=new ArrayList<Object>();
         rowList.add(results.getString(1));
         rowList.add(dateFormat.format(results.getTimestamp(2,dbStorageCalendar)));
         rowList.add(dateFormat.format(results.getTimestamp(3,dbStorageCalendar)));
         rowList.add(dateFormat.format(results.getTimestamp(4,dbStorageCalendar)));
         rowList.add(results.getDouble(5));
         rowList.add(results.getString(6));
         resultList.add(rowList);
     }
     results.close();
 }catch(Exception ex){
     
 }finally{
     statement.close();
 }
 
 StringBuffer strBuf=new StringBuffer();
 strBuf.append("{");
 strBuf.append("\"version\":\"20170221\"");
 strBuf.append(",\"xauthor\":\"zhaoxp\"");
 strBuf.append(",\"rowsize\":"+resultList.size());
 strBuf.append(",\"resultlist\":[");
 for(int i=0;i<resultList.size();i++){
     ArrayList rowList=resultList.get(i);
     strBuf.append("[");
     strBuf.append("\""+rowList.get(0)+"\",");
     strBuf.append("\""+rowList.get(1)+"\",");
     strBuf.append("\""+rowList.get(2)+"\",");
     strBuf.append("\""+rowList.get(3)+"\",");
     strBuf.append(""+rowList.get(4)+",");
     strBuf.append("\""+rowList.get(5)+"\"");
     strBuf.append("]");
     if(i!=resultList.size()-1){
         strBuf.append(",");
     }
 }
 strBuf.append("]");
 strBuf.append("");
 
 strBuf.append("}");
 //out.println(strBuf);
 
 //print
 response.setContentType("application/json");
 response.setCharacterEncoding("UTF-8");
 
 PrintWriter printWriter = response.getWriter();
 printWriter.print(strBuf);
 printWriter.flush();
 printWriter.close();
 
%>

<%!
 static final Calendar  dbStorageCalendar = new GregorianCalendar( TimeZone.getTimeZone( "GMT" ) );

 static String  asNonNull( final Object object )
 {
   return ( ( object != null ) ? object.toString() : "" );
 }
%>